package com.javasm.finance.dao.impl;

import com.javasm.finance.dao.UserDao;
import com.javasm.finance.entity.User;
import com.javasm.finance.entity.UserMenu;
import com.javasm.finance.util.DBUtils;
import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.DbUtils;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ColumnListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserDaoImpl implements UserDao {
    QueryRunner runner=new QueryRunner();

    @Override
    public User findLoginUser(String userName, String userPwd) throws SQLException {
        Connection conn = DBUtils.getConn();
        String sql="select user_id,user_name,user_pwd from fin_admin_user where user_name=? and user_pwd=?;";
        BeanHandler<User> handler = new BeanHandler<>(User.class, new BasicRowProcessor(new GenerousBeanProcessor()));
        User user = runner.query(conn, sql, handler, userName, userPwd);
        DBUtils.getClose(conn,null,null);
        return user;
    }

    @Override
    public List<String> findLoginUserMenuUrl(Integer userId) throws SQLException {
        Connection conn = DBUtils.getConn();
        String sql = "select am.menu_url " +
                "from fin_user_menu um,fin_admin_menu am " +
                "where um.menu_id=am.menu_id " +
                "and am.parent_id!=0 " +
                "and um.user_id=?";
        ColumnListHandler<String> columnListHandler = new ColumnListHandler<>("menu_url");
        List<String> loginUserMenuUrl = runner.query(conn, sql, columnListHandler, userId);
        DBUtils.getClose(conn,null,null);
        return loginUserMenuUrl;
    }

    @Override
    public List<User> findUser(Integer page, Integer pageSize, User user) throws SQLException {
        Connection conn = DBUtils.getConn();
        String sql = "select user_id,user_name,reg_time,login_time,is_valid,head_img " +
                "from fin_admin_user ";
        //SQL连接词
        boolean isWhere = true;
        //条件  ?
        List paramList = new ArrayList();
        //用户编号
        if (user.getUserId() != null && !"".equals(user.getUserId())) {
            if (isWhere) {
                sql += "where ";
                isWhere = false;
            } else {
                sql += "and ";
            }
            sql += "user_id=? ";
            paramList.add(user.getUserId());
        }
        //用户名称
        if (user.getUserName() != null && !"".equals(user.getUserName())) {
            if (isWhere) {
                sql += "where ";
                isWhere = false;
            } else {
                sql += "and ";
            }
            sql += "user_name like ? ";
            paramList.add("%" + user.getUserName() + "%");
        }
        //分页查询  m=(page-1)*pageSize  n=pageSize
        sql += "limit " + (page - 1) * pageSize + "," + pageSize;
        BeanListHandler<User> beanListHandler = new BeanListHandler<>(User.class, new BasicRowProcessor(new GenerousBeanProcessor()));
        List<User> userList = runner.query(conn, sql, beanListHandler, paramList.toArray());
        DBUtils.getClose(conn, null, null);
        return userList;
    }

    @Override
    public long findTotalNum(User user) throws SQLException {
        Connection conn = DBUtils.getConn();
        String sql = "select count(1) totalNum from fin_admin_user ";
        //SQL连接词
        boolean isWhere = true;
        //条件  ?
        List paramList = new ArrayList();
        //用户编号
        if (user.getUserId() != null && !"".equals(user.getUserId())) {
            if (isWhere) {
                sql += "where ";
                isWhere = false;
            } else {
                sql += "and ";
            }
            sql += "user_id=? ";
            paramList.add(user.getUserId());
        }
        //用户名称
        if (user.getUserName() != null && !"".equals(user.getUserName())) {
            if (isWhere) {
                sql += "where ";
                isWhere = false;
            } else {
                sql += "and ";
            }
            sql += "user_name like ? ";
            paramList.add("%" + user.getUserName() + "%");
        }
        long totalNum = runner.query(conn, sql, new ScalarHandler<>(), paramList.toArray());
        DBUtils.getClose(conn, null, null);
        return totalNum;
    }

    @Override
    public int addUser(User user) throws SQLException {
        Connection conn = DBUtils.getConn();
        String sql = "insert into fin_admin_user(user_name,user_pwd,is_valid,role_id,reg_time,head_img) values (?,'1234',?,?,?,?)";
        int rows = runner.update(conn, sql, user.getUserName(), user.getIsValid(), user.getRoleId(), user.getRegTime(),user.getHeadImg());
        DBUtils.getClose(conn, null, null);
        return rows;
    }

    @Override
    public User findByUid(Integer userId) throws SQLException {
        Connection conn = DBUtils.getConn();
        String sql = "select user_id,user_name,user_pwd,is_valid,role_id,reg_time,head_img " +
                "from fin_admin_user " +
                "where user_id=?";
        BeanHandler<User> beanHandler = new BeanHandler<>(User.class, new BasicRowProcessor(new GenerousBeanProcessor()));
        User user = runner.query(conn, sql, beanHandler, userId);
        DBUtils.getClose(conn, null, null);
        return user;
    }

    @Override
    public int editUser(User user) throws SQLException {
        Connection conn = DBUtils.getConn();
        String sql = "update " +
                "fin_admin_user " +
                "set " +
                "user_name=?,is_valid=?,role_id=?,reg_time=?,head_img=? " +
                "where " +
                "user_id=?";
        int rows = runner.update(conn, sql, user.getUserName(), user.getIsValid(), user.getRoleId(), user.getRegTime(), user.getHeadImg(), user.getUserId());
        DBUtils.getClose(conn, null, null);
        return rows;
    }

    @Override
    public int deleteUser(Integer userId, Connection conn) throws SQLException {
        String sql = "delete from fin_admin_user where user_id=?";
        int rows = runner.update(conn, sql, userId);
        return rows;
    }

    @Override
    public List<UserMenu> findUserMenu(Integer userId) throws SQLException {
        Connection conn = DBUtils.getConn();
        String sql = "select user_id,menu_id from fin_user_menu where user_id=?";
        BeanListHandler<UserMenu> beanListHandler = new BeanListHandler<>(UserMenu.class, new BasicRowProcessor(new GenerousBeanProcessor()));
        List<UserMenu> userMenuList = runner.query(conn, sql, beanListHandler, userId);
        DBUtils.getClose(conn, null, null);
        return userMenuList;
    }

    @Override
    public int deleteUserMenu(Integer userId, Connection conn) throws SQLException {
        String sql = "delete from fin_user_menu where user_id=?";
        int rows = runner.update(conn, sql, userId);
        return rows;
    }

    @Override
    public int editLoginTime(Integer userId) throws SQLException {
        Connection conn = DBUtils.getConn();
        String sql = "update fin_admin_user set login_time=now() where user_id=?";
        int rows = runner.update(conn, sql, userId);
        DBUtils.getClose(conn, null, null);
        return rows;
    }
}
